innodb以表為單位來搜集統計資料,分為兩種資料
當我們選擇把某表及該表索引的統計資料存放在磁碟中,實際上是把這些統計資料存在下面兩個表中。
mysql> show tables from mysql like 'innodb%stats';
+--------------------------------+
| Tables_in_mysql (innodb%stats) |
+--------------------------------+
| innodb_index_stats |
| innodb_table_stats |
+--------------------------------+
2 rows in set (0.11 sec)
innodb_table_stats:存放表的統記資料,每筆紀錄對應著一個表的統計資料。
innodb_index_stats:存放索引的統記資料,每筆紀錄對應著一個索引的統計項資料。
mysql> select * from mysql.innodb_table_stats;
+---------------+----------------------+---------------------+----------+----------------------+--------------------------+
| database_name | table_name | last_update | n_rows | clustered_index_size | sum_of_other_index_sizes |
+---------------+----------------------+---------------------+----------+----------------------+--------------------------+
| mysql | component | 2021-09-02 12:49:09 | 1 | 1 | 0 |
| ryan_demo_db | per2 | 2021-09-10 18:15:15 | 18329781 | 50688 | 0 |
| ryan_demo_db | ryan_demo2_table | 2021-09-07 16:29:23 | 16 | 1 | 0 |
| ryan_demo_db | ryan_demo_table | 2021-09-07 15:41:08 | 16 | 1 | 0 |
| ryan_demo_db | score | 2021-09-20 14:28:38 | 4 | 1 | 0 |
| ryan_demo_db | single_table | 2021-09-10 13:56:05 | 10146 | 97 | 144 |
| ryan_demo_db | single_table2 | 2021-09-24 12:55:16 | 0 | 1 | 4 |
| ryan_demo_db | student | 2021-09-20 14:18:24 | 3 | 1 | 0 |
| ryan_demo_db | t1 | 2021-09-20 13:52:33 | 3 | 1 | 0 |
| ryan_demo_db | t2 | 2021-09-20 13:52:54 | 2 | 1 | 0 |
| sys | sys_config | 2018-07-17 11:08:23 | 6 | 1 | 0 |
+---------------+----------------------+---------------------+----------+----------------------+--------------------------+
可以直接看到資料庫下每個表的紀錄筆數(n_rows),以及表的聚簇索引所佔用的頁面數(clustered_index_size)和表的其他索引所佔用的頁面數(sum_of_other_index_sizes)。
mysql> select * from mysql.innodb_index_stats;
+---------------+----------------------+-----------------------------+---------------------+--------------+------------+-------------+-----------------------------------+
| database_name | table_name | index_name | last_update | stat_name | stat_value | sample_size | stat_description |
+---------------+----------------------+-----------------------------+---------------------+--------------+------------+-------------+-----------------------------------+
| ryan_demo_db | per2 | GEN_CLUST_INDEX | 2021-09-10 18:15:15 | n_diff_pfx01 | 18329781 | 20 | DB_ROW_ID |
| ryan_demo_db | per2 | GEN_CLUST_INDEX | 2021-09-10 18:15:15 | n_leaf_pages | 50544 | NULL | Number of leaf pages in the index |
| ryan_demo_db | per2 | GEN_CLUST_INDEX | 2021-09-10 18:15:15 | size | 50688 | NULL | Number of pages in the index |
| ryan_demo_db | ryan_demo2_table | GEN_CLUST_INDEX | 2021-09-07 16:29:23 | n_diff_pfx01 | 16 | 1 | DB_ROW_ID |
| ryan_demo_db | ryan_demo2_table | GEN_CLUST_INDEX | 2021-09-07 16:29:23 | n_leaf_pages | 1 | NULL | Number of leaf pages in the index |
| ryan_demo_db | ryan_demo2_table | GEN_CLUST_INDEX | 2021-09-07 16:29:23 | size | 1 | NULL | Number of pages in the index |
| ryan_demo_db | ryan_demo_table | GEN_CLUST_INDEX | 2021-09-07 15:41:08 | n_diff_pfx01 | 16 | 1 | DB_ROW_ID |
| ryan_demo_db | ryan_demo_table | GEN_CLUST_INDEX | 2021-09-07 15:41:08 | n_leaf_pages | 1 | NULL | Number of leaf pages in the index |
| ryan_demo_db | ryan_demo_table | GEN_CLUST_INDEX | 2021-09-07 15:41:08 | size | 1 | NULL | Number of pages in the index |
| ryan_demo_db | score | PRIMARY | 2021-09-20 14:28:38 | n_diff_pfx01 | 2 | 1 | number |
| ryan_demo_db | score | PRIMARY | 2021-09-20 14:28:38 | n_diff_pfx02 | 4 | 1 | number,subject |
| ryan_demo_db | score | PRIMARY | 2021-09-20 14:28:38 | n_leaf_pages | 1 | NULL | Number of leaf pages in the index |
| ryan_demo_db | score | PRIMARY | 2021-09-20 14:28:38 | size | 1 | NULL | Number of pages in the index |
| ryan_demo_db | single_table | PRIMARY | 2021-09-10 13:56:05 | n_diff_pfx01 | 10146 | 20 | id |
| ryan_demo_db | single_table | PRIMARY | 2021-09-10 13:56:05 | n_leaf_pages | 70 | NULL | Number of leaf pages in the index |
| ryan_demo_db | single_table | PRIMARY | 2021-09-10 13:56:05 | size | 97 | NULL | Number of pages in the index |
| ryan_demo_db | single_table | idx_key1 | 2021-09-10 13:56:05 | n_diff_pfx01 | 10000 | 17 | key1 |
| ryan_demo_db | single_table | idx_key1 | 2021-09-10 13:56:05 | n_diff_pfx02 | 10000 | 17 | key1,id |
| ryan_demo_db | single_table | idx_key1 | 2021-09-10 13:56:05 | n_leaf_pages | 17 | NULL | Number of leaf pages in the index |
| ryan_demo_db | single_table | idx_key1 | 2021-09-10 13:56:05 | size | 18 | NULL | Number of pages in the index |
| ryan_demo_db | single_table | idx_key3 | 2021-09-10 13:56:05 | n_diff_pfx01 | 10000 | 17 | key3 |
| ryan_demo_db | single_table | idx_key3 | 2021-09-10 13:56:05 | n_diff_pfx02 | 10000 | 17 | key3,id |
| ryan_demo_db | single_table | idx_key3 | 2021-09-10 13:56:05 | n_leaf_pages | 17 | NULL | Number of leaf pages in the index |
| ryan_demo_db | single_table | idx_key3 | 2021-09-10 13:56:05 | size | 18 | NULL | Number of pages in the index |
| ryan_demo_db | single_table | idx_key_part | 2021-09-10 13:56:05 | n_diff_pfx01 | 10000 | 59 | key_part1 |
| ryan_demo_db | single_table | idx_key_part | 2021-09-10 13:56:05 | n_diff_pfx02 | 10000 | 59 | key_part1,key_part2 |
| ryan_demo_db | single_table | idx_key_part | 2021-09-10 13:56:05 | n_diff_pfx03 | 10000 | 59 | key_part1,key_part2,key_part3 |
| ryan_demo_db | single_table | idx_key_part | 2021-09-10 13:56:05 | n_diff_pfx04 | 10000 | 59 | key_part1,key_part2,key_part3,id |
| ryan_demo_db | single_table | idx_key_part | 2021-09-10 13:56:05 | n_leaf_pages | 59 | NULL | Number of leaf pages in the index |
| ryan_demo_db | single_table | idx_key_part | 2021-09-10 13:56:05 | size | 97 | NULL | Number of pages in the index |
| ryan_demo_db | single_table | uk_key2 | 2021-09-10 13:56:05 | n_diff_pfx01 | 10000 | 10 | key2 |
| ryan_demo_db | single_table | uk_key2 | 2021-09-10 13:56:05 | n_leaf_pages | 10 | NULL | Number of leaf pages in the index |
| ryan_demo_db | single_table | uk_key2 | 2021-09-10 13:56:05 | size | 11 | NULL | Number of pages in the index |
| ryan_demo_db | single_table2 | PRIMARY | 2021-09-24 12:55:16 | n_diff_pfx01 | 0 | 1 | id |
| ryan_demo_db | single_table2 | PRIMARY | 2021-09-24 12:55:16 | n_leaf_pages | 1 | NULL | Number of leaf pages in the index |
| ryan_demo_db | single_table2 | PRIMARY | 2021-09-24 12:55:16 | size | 1 | NULL | Number of pages in the index |
| ryan_demo_db | single_table2 | idx_key1 | 2021-09-24 12:55:16 | n_diff_pfx01 | 0 | 1 | key1 |
| ryan_demo_db | single_table2 | idx_key1 | 2021-09-24 12:55:16 | n_diff_pfx02 | 0 | 1 | key1,id |
| ryan_demo_db | single_table2 | idx_key1 | 2021-09-24 12:55:16 | n_leaf_pages | 1 | NULL | Number of leaf pages in the index |
| ryan_demo_db | single_table2 | idx_key1 | 2021-09-24 12:55:16 | size | 1 | NULL | Number of pages in the index |
| ryan_demo_db | single_table2 | idx_key3 | 2021-09-24 12:55:16 | n_diff_pfx01 | 0 | 1 | key3 |
| ryan_demo_db | single_table2 | idx_key3 | 2021-09-24 12:55:16 | n_diff_pfx02 | 0 | 1 | key3,id |
| ryan_demo_db | single_table2 | idx_key3 | 2021-09-24 12:55:16 | n_leaf_pages | 1 | NULL | Number of leaf pages in the index |
| ryan_demo_db | single_table2 | idx_key3 | 2021-09-24 12:55:16 | size | 1 | NULL | Number of pages in the index |
| ryan_demo_db | single_table2 | idx_key_part | 2021-09-24 12:55:16 | n_diff_pfx01 | 0 | 1 | key_part1 |
| ryan_demo_db | single_table2 | idx_key_part | 2021-09-24 12:55:16 | n_diff_pfx02 | 0 | 1 | key_part1,key_part2 |
| ryan_demo_db | single_table2 | idx_key_part | 2021-09-24 12:55:16 | n_diff_pfx03 | 0 | 1 | key_part1,key_part2,key_part3 |
| ryan_demo_db | single_table2 | idx_key_part | 2021-09-24 12:55:16 | n_diff_pfx04 | 0 | 1 | key_part1,key_part2,key_part3,id |
| ryan_demo_db | single_table2 | idx_key_part | 2021-09-24 12:55:16 | n_leaf_pages | 1 | NULL | Number of leaf pages in the index |
| ryan_demo_db | single_table2 | idx_key_part | 2021-09-24 12:55:16 | size | 1 | NULL | Number of pages in the index |
| ryan_demo_db | single_table2 | uk_key2 | 2021-09-24 12:55:16 | n_diff_pfx01 | 0 | 1 | key2 |
| ryan_demo_db | single_table2 | uk_key2 | 2021-09-24 12:55:16 | n_leaf_pages | 1 | NULL | Number of leaf pages in the index |
| ryan_demo_db | single_table2 | uk_key2 | 2021-09-24 12:55:16 | size | 1 | NULL | Number of pages in the index |
| ryan_demo_db | student | PRIMARY | 2021-09-20 14:18:24 | n_diff_pfx01 | 3 | 1 | number |
| ryan_demo_db | student | PRIMARY | 2021-09-20 14:18:24 | n_leaf_pages | 1 | NULL | Number of leaf pages in the index |
| ryan_demo_db | student | PRIMARY | 2021-09-20 14:18:24 | size | 1 | NULL | Number of pages in the index |
| ryan_demo_db | t1 | GEN_CLUST_INDEX | 2021-09-20 13:52:33 | n_diff_pfx01 | 3 | 1 | DB_ROW_ID |
| ryan_demo_db | t1 | GEN_CLUST_INDEX | 2021-09-20 13:52:33 | n_leaf_pages | 1 | NULL | Number of leaf pages in the index |
| ryan_demo_db | t1 | GEN_CLUST_INDEX | 2021-09-20 13:52:33 | size | 1 | NULL | Number of pages in the index |
| ryan_demo_db | t2 | GEN_CLUST_INDEX | 2021-09-20 13:52:54 | n_diff_pfx01 | 2 | 1 | DB_ROW_ID |
| ryan_demo_db | t2 | GEN_CLUST_INDEX | 2021-09-20 13:52:54 | n_leaf_pages | 1 | NULL | Number of leaf pages in the index |
| ryan_demo_db | t2 | GEN_CLUST_INDEX | 2021-09-20 13:52:54 | size | 1 | NULL | Number of pages in the index |
| sys | sys_config | PRIMARY | 2018-07-17 11:08:23 | n_diff_pfx01 | 6 | 1 | variable |
| sys | sys_config | PRIMARY | 2018-07-17 11:08:23 | n_leaf_pages | 1 | NULL | Number of leaf pages in the index |
| sys | sys_config | PRIMARY | 2018-07-17 11:08:23 | size | 1 | NULL | Number of pages in the index |
+---------------+----------------------+-----------------------------+---------------------+--------------+------------+-------------+-----------------------------------+
可以直接看到索引下的統計項名稱(stat_name)、數量(stat_value)及對應的取樣頁面數(sample_size)。
統計項名稱有以下三種:
而這兩個表可以透過設定參數自動更新也可以手動執行更新,這邊不再多做了解。
當系統變數innodb_stats_persistent設為off的時候,之後創建的表就都是非永久性的。
但由於後續的新版本已經很少使用這個方式,所以也就不深入說明它了。